﻿using ConmonDateModel;
using DateDBControl.Model;
using Google.Protobuf.Compiler;
using MyLogProject;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Net.PeerToPeer;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace DateDBControl
{
    public static class SqlsControl
    {

        #region Login ans OutLogin
        public static string GetToLoginSql(bool IsUpdate,string _uuid,string _deviceid,DateTime _loginTime, string _currentVersion,string _ipaddress,bool _isLogInByDLL,int isOnLine=1) 
        {
            string sql = "";
            int iscommelogdll = _isLogInByDLL ? 1 : 0;
            string _insert_sql = $"insert b_device(uuid,deviceid,logintime,currentverion,ipaddress,isonline,iscommelogdll)" +$"values('{_uuid}','{_deviceid}','{_loginTime}','{_currentVersion}','{_ipaddress}','{isOnLine}','{iscommelogdll}')";

            string _update_sql = $"UPDATE b_device SET logintime='{_loginTime}' , isonline='{isOnLine}',currentverion='{_currentVersion}',ipaddress='{_ipaddress}',iscommelogdll='{iscommelogdll}' WHERE deviceid='{_deviceid}';";

            sql = IsUpdate ? _update_sql : _insert_sql;
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetToLoginSql", sql);

            return sql;
        }


        public static string GetLoginOutSql(string _deviceid, DateTime _loginOutTime, int isOnLine = 0) 
        {
            string _update_sql = $"UPDATE b_device SET logouttime='{_loginOutTime}', isonline='{isOnLine}' WHERE deviceid='{_deviceid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetLoginOutSql", _update_sql);
            return _update_sql;
        }



        public static string GetCheckDeviceExitsSql(string _deviceid) 
        {
            string sql = $"select id from b_device where deviceid='{_deviceid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCheckDeviceExitsSql", sql);
            return sql;
        }
        #endregion

        #region GetDevice

        public static string GetAllDeviceCountSql()
        {
            string sql = $"SELECT Count(*) from {ComnonStaticModel.DeviceDbName}  ORDER BY id DESC ;";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllDeviceSql", sql);

            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_deviceid"></param>
        /// <param name="inputpageCode">当前页</param>
        /// <param name="inputPageSize">每页显示数量</param>
        /// <returns></returns>
        public static string GetCurrentPageDeviceSql(int inputpageCode, int inputPageSize)
        {
            string sql = $"SELECT * from {ComnonStaticModel.DeviceDbName}  ORDER BY id DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize}";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllDeviceSql", sql);

            return sql;
        }
        #endregion

        #region Upload

      

        #region logs
        public static string GetInsertLogFileSql(string _deviceid, string _uuid,string _filepath,string filename,DateTime updatetime)
        {
            string _insert_sql = $"insert b_logs(deviceid,uuid,filepath,filename,updatetime)" + $"values('{_deviceid}','{_uuid}','{_filepath}','{filename}','{updatetime}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetInsertLogFileSql", _insert_sql);
            return _insert_sql;
        }
        #endregion


        #endregion


        #region logs
        public static string GetAllLogsCountSql(string _deviceid)
        {
            string sql = $"SELECT Count(*) from b_logs where deviceid='{_deviceid}'  ORDER BY updatetime DESC ;";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllLogsCountSql", sql);

            return sql;
        }

        public static string GetCurrentPageLogsSql(string _deviceid, int inputpageCode, int inputPageSize)
        {
            string sql = $"SELECT * from b_logs where deviceid='{_deviceid}' ORDER BY updatetime DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize}";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCurrentPageLogsSql", sql);

            return sql;
        }
        #endregion

        #region cmd
        public static string GetUpdateDoCmdSql(string _deviceid,int _cmdtype,string _filduuid, string _fileName, DateTime? logUploadTime)
        {
            string _update_sql = "";
            if (logUploadTime == null)
            {
                 _update_sql = $"UPDATE b_device SET cmdtype='{_cmdtype}',fileuuid='{_filduuid}',fileName='{_fileName}',cmdresulttype='100',uplodlogtime=null WHERE deviceid='{_deviceid}';";

            }
            else 
            {
                _update_sql = $"UPDATE b_device SET cmdtype='{_cmdtype}',fileuuid='{_filduuid}',fileName='{_fileName}',cmdresulttype='100',uplodlogtime='{logUploadTime}' WHERE deviceid='{_deviceid}';";

            }

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateDoCmdSql", _update_sql);
            return _update_sql;
        }

        public static string GetUpdateDoCmdResultSql(string _deviceid,int oldcmdType, int _cmdtypeResult)
        {
            string _update_sql = "";
            if (_cmdtypeResult == Convert.ToInt32(CmdSendResultType.Success))
            {
                _update_sql = $"UPDATE b_device SET cmdtype='{oldcmdType}', cmdresulttype='{_cmdtypeResult}',uplodlogtime=null WHERE deviceid='{_deviceid}';";            
            }
            else 
            {
                _update_sql = $"UPDATE b_device SET cmdtype='{oldcmdType}',cmdresulttype='{_cmdtypeResult}',uplodlogtime=null WHERE deviceid='{_deviceid}';";
            }

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateDoCmdResultSql", _update_sql);
            return _update_sql;
        }



        public static string GetCmdSql(string _deviceid)
        {
            string _update_sql = $"select * from b_device  WHERE deviceid='{_deviceid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCmdSql", _update_sql);
            return _update_sql;
        }
        #endregion

        #region SetNewServerAddress
        public static string GetUpdateServerConfigCmdSql(string _deviceid, string _NewServerIp, string _NewServerPort)
        {
            string _update_sql = "";
            _update_sql = $"UPDATE b_device SET newserverip='{_NewServerIp}',newserverport='{_NewServerPort}' WHERE deviceid='{_deviceid}';";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateServerConfigCmdSql", _update_sql);
            return _update_sql;
        }
        #endregion





        #region New

        public static string CreatCompanyDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
               "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "code VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "companyname VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "linkman VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "linkPhone VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "description VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
                "username VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
                "update_date datetime NULL," +
               "create_date datetime NULL," +
               " UNIQUE (uuid)," +
                " UNIQUE (code)," +
               "PRIMARY KEY (id));", ComnonStaticModel.CompanyName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatCompanyDb", sql);
            return sql;
        }


        public static string CreatDeviceDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
               "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "companyid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "devicename VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "robatmac VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "license VARCHAR(9000) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "effecttime datetime NULL," +
                "username VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "update_date datetime NULL," +
               "create_date datetime NULL," +
                "UNIQUE (uuid)," +
                "UNIQUE (robatmac)," +
               "PRIMARY KEY (id));", ComnonStaticModel.DeviceDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatAuthorizeDb", sql);
            return sql;
        }
        public static string CreatDeviceModuleDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
              "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "deviceid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "moduleid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "modulename VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_general_ci," +   
              "serverversion VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "clientversion VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "update_date datetime NULL," +
              "create_date datetime NULL," +
               " UNIQUE (uuid)," +
              "PRIMARY KEY (id));", ComnonStaticModel.DeviceModeuleDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatDeviceModuleDb", sql);
            return sql;
        }

        public static string CreatModeuleDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
              "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "modulename VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "version VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "filepath VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "filename VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "update_date datetime NULL," +
              "create_date datetime NULL," +
              " UNIQUE (uuid)," +
              "PRIMARY KEY (id));", ComnonStaticModel.ModeuleDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatModeuleDb", sql);
            return sql;
        }
        public static string CreatHistoryModeuleDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
              "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "moduleid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "modulename VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "version VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "filepath VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "filename VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "update_date datetime NULL," +
              "create_date datetime NULL," +
              " UNIQUE (uuid)," +
              "PRIMARY KEY (id));", ComnonStaticModel.HistoryModeuleDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatModeuleDb", sql);
            return sql;
        }

        public static string CreatUserDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
              "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "username VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "nickname VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "password VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "role VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +              
              "update_date datetime NULL," +
              "create_date datetime NULL," +
              " UNIQUE (username)," +
              "PRIMARY KEY (id));", ComnonStaticModel.UserDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatUserDb", sql);
            return sql;
        }

        public static string CreatRoleDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
              "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "role VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "method VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +             
              "update_date datetime NULL," +
              "create_date datetime NULL," +
              " UNIQUE (uuid)," +
              "PRIMARY KEY (id));", ComnonStaticModel.RoleDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatRoleDb", sql);
            return sql;
        }

        public static string CreaLogDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
              "uuid VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "companycode VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "companyname VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "robatmac VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "methodname VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
              "count int(32)," +
              "update_date datetime NULL," +
              "create_date datetime NULL," +
              " UNIQUE (uuid)," +           
              "PRIMARY KEY (id));", ComnonStaticModel.RecodLogName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreaLogDb", sql);
            return sql;
        }

        public static string CreatFileDb()
        {
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0}(id int(11) NOT NULL AUTO_INCREMENT," +
               "version_first int(11) NOT NULL," +
               "version_second int(11) NOT NULL," +
               "version_three int(11) NOT NULL," +
               "version_four int(11) NOT NULL," +
               "filepath VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "filename VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci," +
               "create_date datetime NULL," +                
               "PRIMARY KEY (id));", ComnonStaticModel.FileDbName);
            LogManager.WriteLog(LogTypeEnum.SqlType, "CreatFileDb", sql);
            return sql;
        }


        #region 所有模块操作
        public static string GetAddTargetModule(string _uudi,string _moduleName, string version) 
        {         
            string _insert_sql = $"insert {ComnonStaticModel.ModeuleDbName}(uuid,modulename,version,create_date)" + $"values('{_uudi}','{_moduleName}','{version}','{DateTime.Now}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddTargetModule", _insert_sql);
            return _insert_sql;
        }
        public static string GetDeleteTargetModule(string _uuid)
        {
            string _insert_sql = $"DELETE FROM {ComnonStaticModel.ModeuleDbName} where uuid='{_uuid}'";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeleteTargetModule", _insert_sql);
            return _insert_sql;
        }
        public static string GetAllModule(string _deviceid, string modulename)
        {
            //string _sql = $"select * from  {ComnonStaticModel.ModeuleDbName} ORDER BY id;";
            string _sql = $"select b.clientversion,a.* from {ComnonStaticModel.ModeuleDbName} a left join (select b.clientversion,a.uuid  from {ComnonStaticModel.ModeuleDbName} a left  join {ComnonStaticModel.DeviceModeuleDbName} b on a.uuid = b.moduleid  where b.deviceid ='{_deviceid}' ) b on a.uuid = b.uuid";
            if(!string.IsNullOrEmpty(modulename))
            {
                _sql += $"  where a.modulename like '%{modulename}%'";
            }
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllModule", _sql);
            return _sql;
        }
        #endregion

        #region 用户
        public static string GetAddUser(UserModel usermodel)
        {
            string _insert_sql = $"insert {ComnonStaticModel.UserDbName}(uuid,username,nickname,password,role,update_date,create_date)" + $"values('{usermodel.uuid}','{usermodel.username}','{usermodel.nickname}','{usermodel.password}','{usermodel.role}','{usermodel.update_date}','{usermodel.create_date}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddUser", _insert_sql);
            return _insert_sql;
        }

        public static string GetTargetUser(string username, string password)
        {
            string _sql = $"select * from  {ComnonStaticModel.UserDbName} where username='{username}';";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetUser", _sql);
            return _sql;
        }

        public static string GetTargetUserRole(string _role) 
        {
            string _sql = $"select * from  {ComnonStaticModel.RoleDbName} where role='{_role}';";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetUserRole", _sql);
            return _sql;
        }
        #endregion


        #region 公司操作
        public static string GetAddCompany(string _code, string _uudi, string _companyname,string _linkman,string _linkPhone,string _description, string _username)
        {
            DateTime itemTime = DateTime.Now;
            string _insert_sql = $"insert {ComnonStaticModel.CompanyName}(uuid,code,companyname,linkman,linkPhone,description,username,update_date,create_date)" + $"values('{_uudi}','{_code}','{_companyname}','{_linkman}','{_linkPhone}','{_description}','{_username}','{itemTime}','{itemTime}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddCompany", _insert_sql);
            return _insert_sql;
        }

        public static string GetUpdatedCompany(string _code, string _uudi, string _companyname, string _linkman, string _linkPhone, string _description, string _username)
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.CompanyName} SET code='{_code}',companyname='{_companyname}',linkman='{_linkman}',linkPhone='{_linkPhone}',description='{_description}',username='{_username}',update_date='{DateTime.Now}' WHERE uuid='{_uudi}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdatedCompany", _update_sql);
            return _update_sql;
        }

        public static string GetDeleteCompany(string _uuid)
        {
            string sql = $"DELETE FROM {ComnonStaticModel.CompanyName} WHERE uuid='{_uuid}'";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeleteCompany", sql);
            return sql;
        }


        public static string GetCompanyAllCount()
        {
            string sql = $"SELECT COUNT(id) from {ComnonStaticModel.CompanyName} ;";
     
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCompanyAllCount", sql);

            return sql;
        }


        public static string GetCompanyAllCount(string companycode, string companyname)
        {
            string sql = $"SELECT * from {ComnonStaticModel.CompanyName} ";
            if (!string.IsNullOrEmpty(companycode))
            {
                sql += $"where code like '%{companycode}%'";
                if (!string.IsNullOrEmpty(companyname))
                {
                    sql += $"or companyname like '%{companyname}%'";
                }
            }
            else 
            {
                if (!string.IsNullOrEmpty(companyname))
                {
                    sql += $" where companyname like '%{companyname}%'";
                }
            }
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCompanyAllCount", sql);

            return sql;
        }

        public static string GetCompanyByPage(string companycode, string companyname, int inputpageCode, int inputPageSize)
        {
            string sql = $"SELECT * from {ComnonStaticModel.CompanyName} ";

           
            if (!string.IsNullOrEmpty(companycode))
            {
                sql += $" where code like '%{companycode}%' ";
                if (!string.IsNullOrEmpty(companyname))
                {
                    sql += $" or companyname like '%{companyname}%' ";
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(companyname))
                {
                    sql += $" where companyname like '%{companyname}%' ";
                }
            }
            sql += $" ORDER BY id DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize}";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCompanyByPage", sql);

            return sql;
        }

        public static string GetCompanyBSearch(string _code, string _companyname)
        {
            string sql = $"SELECT * from {ComnonStaticModel.CompanyName} where code like '%{_code}%' and companyname like '%{_companyname}%' ORDER BY id DESC  ";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCompanyBSearch", sql);

            return sql;
        }


        #endregion

        #region 设备操作

        public static string GetAllDeviceCount()
        {
            string sql = $"SELECT COUNT(id) from {ComnonStaticModel.DeviceDbName} ;";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceAllCount", sql);

            return sql;
        }

        public static string GetAddDevicByCompany(string _uuid, string _companycode,string _devicename,string _robatmac,string _license,DateTime? _effecttime, string _username)
        {
            DateTime itemTime = DateTime.Now;
            string _insert_sql = $"insert {ComnonStaticModel.DeviceDbName}(uuid,companyid,devicename,robatmac,license,effecttime,username,update_date,create_date)" + $"values('{_uuid}','{_companycode}','{_devicename}','{_robatmac}','{_license}','{_effecttime}','{_username}','{itemTime}','{itemTime}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddDevicByCompany", _insert_sql);
            return _insert_sql;
        }


        public static string GetUpdateDevic(string _uuid,string _devicename, string _license, DateTime? _effecttime, string _username)
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.DeviceDbName} SET devicename='{_devicename}',license='{_license}',effecttime='{_effecttime}',username='{_username}',update_date='{DateTime.Now}' WHERE uuid='{_uuid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateDevic", _update_sql);
            return _update_sql;
        }

        public static string GetDeleteDeviceByCompany(string _companyid)
        {
            string sql = $"DELETE FROM {ComnonStaticModel.DeviceDbName} WHERE companyid='{_companyid}'";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeleteCompany", sql);
            return sql;
        }

        public static string GetDeleteDeviceByuuid(string _uuid)
        {
            string sql = $"DELETE FROM {ComnonStaticModel.DeviceDbName} WHERE uuid='{_uuid}'";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeleteDeviceByuuid", sql);
            return sql;
        }


        public static string GetDeviceAllCount()
        {
            string sql = $"SELECT * from {ComnonStaticModel.DeviceDbName}";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceAllCount", sql);

            return sql;
        }
        public static string GetTargetDevice(string _uuid)
        {
            string sql = $"SELECT * from {ComnonStaticModel.DeviceDbName} where uuid='{_uuid}'";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceAllCount", sql);

            return sql;
        }
        public static string GetDeviceByPage(int inputpageCode, int inputPageSize)
        {
            string sql = $"SELECT * from {ComnonStaticModel.DeviceDbName}  ORDER BY id DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize}";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceByPage", sql);

            return sql;
        }


        public static string GetDeviceAllCountByCompanyId(string _companyCode, string companyname,string robatmac)
        {
            string sql = $"SELECT c.companyname,d.* from {ComnonStaticModel.DeviceDbName} d LEFT JOIN {ComnonStaticModel.CompanyName} c on  d.companyid=c.code";

            bool IsCanext = false;
            if (!string.IsNullOrEmpty(_companyCode)|| !string.IsNullOrEmpty(companyname) || !string.IsNullOrEmpty(robatmac)) 
            {
                IsCanext = true;
                sql += " Where ";
            }
            if (IsCanext) 
            {
                if (!string.IsNullOrEmpty(_companyCode))
                {
                    sql += $" companyid like '%{_companyCode}%' ";
                    if (!string.IsNullOrEmpty(companyname))
                    {
                        sql += $" or c.companyname like '%{companyname}%' ";
                    }

                    if (!string.IsNullOrEmpty(robatmac))
                    {
                        sql += $" or robatmac like '%{robatmac}%'";
                    }
                }
                else if (!string.IsNullOrEmpty(companyname))
                {
                    sql += $"  c.companyname like '%{companyname}%' ";
                    if (!string.IsNullOrEmpty(robatmac))
                    {
                        sql += $" or robatmac like '%{robatmac}%'";
                    }
                }
                else if (!string.IsNullOrEmpty(robatmac)) 
                {
                    sql += $" robatmac like '%{robatmac}%'";
                }

            }
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceAllCountByCompanyId", sql);

            return sql;
        }

        public static string GetDeviceByCompanyId(string companyCode, string companyname, string robatmac, int inputpageCode, int inputPageSize)
        {
            string sql = $"SELECT c.companyname,d.* from {ComnonStaticModel.DeviceDbName} d LEFT JOIN {ComnonStaticModel.CompanyName} c on  d.companyid=c.code";

            bool IsCanext = false;
            if (!string.IsNullOrEmpty(companyCode) || !string.IsNullOrEmpty(companyname) || !string.IsNullOrEmpty(robatmac))
            {
                IsCanext = true;
                sql += " Where ";
            }
            if (IsCanext)
            {
                if (!string.IsNullOrEmpty(companyCode))
                {
                    sql += $" companyid like '%{companyCode}%' ";
                    if (!string.IsNullOrEmpty(companyname))
                    {
                        sql += $" or c.companyname like '%{companyname}%' ";
                    }

                    if (!string.IsNullOrEmpty(robatmac))
                    {
                        sql += $" or robatmac like '%{robatmac}%'";
                    }
                }
                else if (!string.IsNullOrEmpty(companyname))
                {
                    sql += $"  c.companyname like '%{companyname}%' ";
                    if (!string.IsNullOrEmpty(robatmac))
                    {
                        sql += $" or robatmac like '%{robatmac}%'";
                    }
                }
                else if (!string.IsNullOrEmpty(robatmac))
                {
                    sql += $" robatmac like '%{robatmac}%'";
                }

            }



            sql += $"  ORDER BY id DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize}";


            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceByCompanyId", sql);

            return sql;
        }

        public static string GetTargetDeviceByrobatmac(string robatmac)
        {
            string sql = $"SELECT c.companyname,d.* from {ComnonStaticModel.DeviceDbName} d LEFT JOIN {ComnonStaticModel.CompanyName} c on  d.companyid=c.code where  robatmac='{robatmac}';";
      
      

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetDeviceByrobatmac", sql);

            return sql;
        }

        #endregion

        #region 设备模块
        public static string GetAddDevicModule(string _devieid, string _uuid, string _moduleid,string _modulename, string _version)
        {
            string _insert_sql = $"insert {ComnonStaticModel.DeviceModeuleDbName}(uuid,deviceid,moduleid,modulename,serverversion,clientversion,create_date)" + $"values('{_uuid}','{_devieid}','{_moduleid}','{_modulename}','{_version}','','{DateTime.Now}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddDevicModule", _insert_sql);
            return _insert_sql;
        }

        public static string GetUpdateDeviceModule(string _deviceid, string _moduleid, string _version)
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.DeviceModeuleDbName} SET clientversion='{_version}',update_date='{DateTime.Now}' WHERE deviceid='{_deviceid}' and moduleid='{_moduleid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateDeviceModule", _update_sql);
            return _update_sql;
        }

        public static string GetDeleteDeviceModuled(string _deviceid)
        {
            string sql = $"DELETE FROM {ComnonStaticModel.DeviceModeuleDbName} WHERE deviceid='{_deviceid}'";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeleteDeviceByuuid", sql);
            return sql;
        }

        public static string GetDeviceModuledExits(string _deviceid)
        {
            string sql = $"select  * FROM {ComnonStaticModel.DeviceModeuleDbName} WHERE deviceid='{_deviceid}'";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetDeviceModuledExits", sql);
            return sql;
        }

        public static string GetTargetModule(string _moduleid)
        {
            string sql = $"SELECT * from {ComnonStaticModel.ModeuleDbName} where uuid='{_moduleid}'";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetModule", sql);

            return sql;
        }

        public static string GetUpdateModule(string _moduleid, string _fileName, string _filePath, string _version)
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.ModeuleDbName} SET filepath='{_filePath}',filename='{_fileName}',version='{_version}',update_date='{DateTime.Now}' WHERE uuid='{_moduleid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateModule", _update_sql);
            return _update_sql;
        }


        public static string GetAddHistoryModuleSql(string _uuid,string _moduleid,string _moduleName, string _fileName, string _filePath, string _version)
        {
       
            string _insert_sql = "";
            _insert_sql = $"insert {ComnonStaticModel.HistoryModeuleDbName}(uuid,moduleid,modulename,version,filepath,filename,update_date,create_date)" + $"values('{_uuid}','{_moduleid}','{_moduleName}','{_version}','{_filePath}','{_fileName}','{DateTime.Now}','{DateTime.Now}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddHistoryModuleSql", _insert_sql);
            return _insert_sql;
        }


        public static string GetTargetModuleByUpdate(string _robatmac)
        {
            string sql = $"SELECT d.id, d.moduleid,d.modulename,dev.iscanupdate,m.version as serverversion,m.filepath,m.filename,d.clientversion FROM {ComnonStaticModel.DeviceModeuleDbName} d  LEFT JOIN {ComnonStaticModel.DeviceDbName} dev on dev.uuid=d.deviceid LEFT JOIN modeulemanager m on m.uuid=d.moduleid  WHERE dev.robatmac='{_robatmac}'";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetModuleByUpdate", sql);

            return sql;
        }

        public static string GetHistoryModuleByPage(string moduleid, int inputpageCode, int inputPageSize)
        {
            string sql = "";
            sql = $"SELECT * from {ComnonStaticModel.HistoryModeuleDbName} where moduleid='{moduleid}'  ORDER BY id DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize}";


            LogManager.WriteLog(LogTypeEnum.SqlType, "GetHistoryModuleByPage", sql);

            return sql;
        }
        #endregion


        #region 日志
        public static string GetAddLogSql(LogManagerModel _LogManagerModel)
        {
            string _insert_sql = $"insert {ComnonStaticModel.RecodLogName}(uuid,companycode,companyname,robatmac,methodname,count,update_date,create_date)" + $"values('{_LogManagerModel.uuid}','{_LogManagerModel.companycode}','{_LogManagerModel.companyname}','{_LogManagerModel.robatmac}','{_LogManagerModel.methodname}',{_LogManagerModel.count},'{_LogManagerModel.update_date}','{_LogManagerModel.create_date}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddLogSql", _insert_sql);
            return _insert_sql;
        }

        public static string GetTargetLogSql(string _robatmac,string _methodname)
        {
            string sql = $"select * from {ComnonStaticModel.RecodLogName} where robatmac='{_robatmac}' and methodname='{_methodname}' ;";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetLogSql", sql);
            return sql;
        }

        public static string GetUpdateTargetLogSql(string _robatmac, string _methodname,int _count)
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.RecodLogName} SET count='{_count}',update_date='{DateTime.Now}' WHERE robatmac='{_robatmac}' and methodname='{_methodname}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateTargetLogSql", _update_sql);
            return _update_sql;
        }

        public static string GetAllLogSql()
        {
            string sql = $"select Count(id) from {ComnonStaticModel.RecodLogName} ;";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllLogSql", sql);
            return sql;
        }
        public static string GetAllLogByPageSql(int inputpageCode, int inputPageSize)
        {
            string sql = $"select d.devicename,lg.* from {ComnonStaticModel.RecodLogName} lg LEFT JOIN  devicemanager d on d.robatmac=lg.robatmac ORDER BY id DESC limit {(inputpageCode - 1) * inputPageSize},{inputPageSize};";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllLogByPageSql", sql);
            return sql;
        }
        #endregion


        public static string GetCheckMacExitsSql(string _macinfo)
        {
            string sql = $"select id from {ComnonStaticModel.DeviceDbName} where macinfo='{_macinfo}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCheckMacExitsSql", sql);
            return sql;
        }

        public static string GetUpdateMacSql(string _MacInfo,string _encryinfo, string _CompanyName, DateTime _EffectTime,  string DescripMsg) 
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.DeviceDbName} SET encryinfo='{_encryinfo}',companyname='{_CompanyName}',effecttime='{_EffectTime}',descripmsg='{DescripMsg}' WHERE macinfo='{_MacInfo}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateMacSql", _update_sql);
            return _update_sql;
        }

        public static string GetUpdateVersionSql(string _MacInfo, string _version)
        {
            string _update_sql = $"UPDATE {ComnonStaticModel.DeviceDbName} SET version='{_version}' WHERE macinfo='{_MacInfo}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetUpdateMacSql", _update_sql);
            return _update_sql;
        }

        public static string GetAddMacSql(string _MacInfo, string _encryinfo,  string DescripMsg,string _version) 
        {
            string _insert_sql = "";
            _insert_sql = $"insert {ComnonStaticModel.DeviceDbName}(macinfo,encryinfo,descripmsg,version,create_date)" + $"values('{_MacInfo}','{_encryinfo}','{DescripMsg}','{_version}','{DateTime.Now}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAddMacSql", _insert_sql);
            return _insert_sql;
        }


        public static string GetTargetMacSql(string _MacInfo) 
        {
            string sql = $"select * from {ComnonStaticModel.DeviceDbName} where robatmac='{_MacInfo}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetMacSql", sql);
            return sql;
        }

   

        #region versionFile
        public static string GetCheckVersionIsExitsSql(int _version_first)
        {
            string _sql = $"select * from {ComnonStaticModel.FileDbName} where version_first>='{_version_first}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetCheckVersionIsExits", _sql);
            return _sql;
        }
        public static string GetAllFileSql()
        {
            string _sql = $"select * from {ComnonStaticModel.FileDbName} ;";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllFileSql", _sql);
            return _sql;
        }

        public static string GetTargetFileSql(int _version_first, int _version_second, int _version_three, int _version_four)
        {
            string _sql = $"select * from {ComnonStaticModel.FileDbName} where version_first='{_version_first}' and version_second='{_version_second}' and version_three='{_version_three}' and version_four='{_version_four}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetFileSql", _sql);
            return _sql;
        }

        public static string GetInsertVerionFileSql(int _version_first, int _version_second, int _version_three, int _version_four, string _filepath, string filename, DateTime updatetime)
        {
            string _insert_sql = $"insert {ComnonStaticModel.FileDbName}(version_first,version_second,version_three,version_four,filepath,filename,create_date)" + $"values('{_version_first}','{_version_second}','{_version_three}','{_version_four}','{_filepath}','{filename}','{updatetime}')";

            LogManager.WriteLog(LogTypeEnum.SqlType, "GetInsertVerionFileSql", _insert_sql);
            return _insert_sql;
        }
        public static string GetAllFileVersionSql(string _deviceid)
        {
            string _sql = $"select * from b_file where deviceid='{_deviceid}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetAllFileVersionSql", _sql);
            return _sql;
        }

        public static string GetTargetFileVersionSql(string _deviceid, string _fileuuid)
        {
            string _sql = $"select * from b_file where deviceid='{_deviceid}' and uuid='{_fileuuid}' ;";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetFileVersionSql", _sql);
            return _sql;
        }



        public static string GetTargetLogFileSql(string _deviceid, string _fileName)
        {
            string _sql = $"select * from b_logs where deviceid='{_deviceid}' and filename='{_fileName}';";
            LogManager.WriteLog(LogTypeEnum.SqlType, "GetTargetLogFileSql", _sql);
            return _sql;
        }

        #endregion
        #endregion
    }
}
